package com.bdqn.easybuy1.dao.impl;

import com.bdqn.easybuy1.dao.BaseDao;
import com.bdqn.easybuy1.dao.ProductDao;
import com.bdqn.easybuy1.entities.Product;
import com.bdqn.easybuy1.utils.EmptyUtils;
import com.bdqn.easybuy1.utils.Pager;
import org.apache.commons.dbutils.DbUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl extends BaseDao<Product> implements ProductDao {
    /**
     * 更新产品库存
     *
     * @param conn     数据库连接对象
     * @param id       产品id
     * @param quantity 更新后的数量
     * @return 返回受影响的行数
     * @throws Exception
     */
    @Override
    public int updateStock(Connection conn, Integer id, Integer quantity) throws Exception {
        String sql = " UPDATE `easybuy_product` SET `stock` = ? WHERE `id` = ? ";
        return super.update(conn, sql, quantity, id);
    }

    /**
     * 添加一个产品信息
     *
     * @param conn    数据库连接对象
     * @param product 封装产品信息的对象
     * @return 返回受影响的行数
     * @throws Exception
     */
    @Override
    public int insert(Connection conn, Product product) throws Exception {
        String sql = "INSERT INTO `easybuy_product`(`name`,`description`,`price`,`stock`,`categoryLevel1Id`,`categoryLevel2Id`,`categoryLevel3Id`,`fileName`,`isDelete`) VALUES(?,?,?,?,?,?,?,?,?) ";
        return super.update(conn, sql, product.getName(),
                product.getDescription(),
                product.getPrice(),
                product.getStock(),
                product.getCategoryLevel1Id(),
                product.getCategoryLevel2Id(),
                product.getCategoryLevel3Id(),
                product.getFileName(),
                product.getIsDelete());
    }

    /**
     * 更新产品信息
     *
     * @param conn    数据库连接对象
     * @param product 封装要修改产品信息的对象
     * @return 返回受影响的行数
     * @throws Exception
     */
    @Override
    public int update(Connection conn, Product product) throws Exception {
        String sql = " UPDATE `easybuy_product` SET `name` = ?,`fileName` = ?,`categoryLevel1Id` = ?,`categoryLevel2Id` = ?,`categoryLevel3Id` = ? WHERE `id` = ? ";
        return super.update(conn, sql, product.getName(),
                product.getFileName(),
                product.getCategoryLevel1Id(),
                product.getCategoryLevel2Id(),
                product.getCategoryLevel3Id(),
                product.getId());
    }

    /**
     * 根据产品id删除指定产品
     *
     * @param conn 数据库连接对象
     * @param id   要删除产品的id
     * @return 返回受影响的行数
     * @throws Exception
     */
    @Override
    public int deleteProductById(Connection conn, Integer id) throws Exception {
        String sql = " DELETE FROM `easybuy_product` WHERE `id` = ? ";
        return super.update(conn, sql, id);
    }

    /**
     * 根据产品的id查询产品对象
     *
     * @param conn 数据库连接对象
     * @param id   产品id
     * @return 返回产品对象
     * @throws Exception
     */
    @Override
    public Product getProductById(Connection conn, Integer id) throws Exception {
        String sql = "SELECT `id`,`name`,`description`,`price`,`stock`,`categoryLevel1Id`,`categoryLevel2Id`,`categoryLevel3Id`,`fileName`,`isDelete` FROM `easybuy_product` WHERE `id` = ? ";
        return getBean(conn, sql, id);
    }

    /**
     * 分页查询产品信息
     *
     * @param conn          数据库连接对象
     * @param currentPageNo 当前页
     * @param pageSize      每页显示数据量大小
     * @param proName       产品名称
     * @param categoryId    分类id
     * @param level         分类级别
     * @return 返回产品的列表
     * @throws Exception
     */
    @Override
    public List<Product> getProductList(Connection conn, Integer currentPageNo, Integer pageSize, String proName, Integer categoryId, Integer level) throws Exception {
        // 根据后面实际的需求再写
        List<Object> paramsList = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer(" SELECT `id`,`name`,`description`,`price`,`stock`,`categoryLevel1Id`,`categoryLevel2Id`,`categoryLevel3Id`,`fileName`,`isDelete` FROM `easybuy_product` WHERE 1=1 ");
        try {
            if (EmptyUtils.isNotEmpty(proName)) {
                sql.append(" AND `name` LIKE ? ");
                paramsList.add("%" + proName + "%");
            }

            if (EmptyUtils.isNotEmpty(categoryId)) {
                sql.append(" AND (`categoryLevel1Id` = ? OR `categoryLevel2Id`=? OR `categoryLevel3Id`=? ) ");
                paramsList.add(categoryId);
                paramsList.add(categoryId);
                paramsList.add(categoryId);
            }
            Integer total = queryProductCount(conn, proName, categoryId, level);
            Pager pager = new Pager(total, pageSize, currentPageNo);
            sql.append(" LIMIT  " + (pager.getCurrentPage() - 1) * pager.getRowPerPage() + "," + pager.getRowPerPage());

            return super.getBeanList(conn, sql.toString(), paramsList.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 查询分页产品的总条数
     *
     * @param conn       数据库连接对象
     * @param proName    产品名称
     * @param categoryId 分类id
     * @param level      分类的级别
     * @return
     * @throws Exception
     */
    @Override
    public Integer queryProductCount(Connection conn, String proName, Integer categoryId, Integer level) throws Exception {

        List<Object> paramsList = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer("  select count(*) count from easybuy_product where 1=1 ");
        if (EmptyUtils.isNotEmpty(proName)) {
            sql.append(" and name like ? ");
            paramsList.add("%" + proName + "%");
        }

        if (EmptyUtils.isNotEmpty(categoryId)) {
            sql.append(" and (categoryLevel1Id = ? or categoryLevel2Id=? or categoryLevel3Id=? ) ");
            paramsList.add(categoryId);
            paramsList.add(categoryId);
            paramsList.add(categoryId);
        }

        Long totalCount = getValue(conn, sql.toString(), paramsList.toArray());

        return Integer.parseInt(totalCount.toString());
    }
}
